package com.windfone.dao.impl.jdbc.mysql;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowCallbackHandler;

import com.windfone.common.Constant;
import com.windfone.dao.AskReponseDao;
import com.windfone.model.Askreponse;

public class AskReponseDaoImpl implements AskReponseDao {
	private JdbcTemplate jdbcTemplate;
	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) { 
		this.jdbcTemplate = jdbcTemplate;
	}

	String INSERT_ASK_SQL = " INSERT INTO askreponse (msgtype,productId,askId ,user,askcontent)  VALUES ( ?,?,?,?,? ) ";
	public int addAsk(final Askreponse askreponse) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
				INSERT_ASK_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						int i=1;
						ps.setInt(i++,askreponse.getMsgtype());
						ps.setInt(i++,askreponse.getProductId());
						ps.setInt(i++,askreponse.getAskId());
						ps.setString(i++,askreponse.getUser());
						ps.setString(i++,askreponse.getAskcontent());
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;	
	}

	public int updateAsk(Askreponse askreponse) {
		return 0;
	}

	String DELETE_SQL = " DELETE FROM  askreponse WHERE id=? ";
	public int deleAsk(final Askreponse askreponse) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
				DELETE_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setInt(1, askreponse.getId());
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}
	
	String REPONSE_ASK_SQL = " UPDATE askreponse SET  reponsecontent=?, reponseName=?, reponsetime=? ,dealFlag=1  WHERE id=? ";
	public int reponseAsk(final Askreponse askreponse) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
				REPONSE_ASK_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						int i=1;
						ps.setString(i++,askreponse.getReponsecontent());
						ps.setString(i++,askreponse.getReponseName());
						ps.setTimestamp(i++, new java.sql.Timestamp( new Date().getTime()));
						ps.setInt(i++, askreponse.getId());
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;	
	}

	public int upReponseAsk(Askreponse askreponse) {
		
		return 0;
	}

	String GET_BY_USERID_SQL = "  SELECT * FROM  askreponse WHERE  dealFlag=1 AND productId=? AND msgtype=? ORDER By id ASC ";
 
	public List<Askreponse> getAskReponseByProductAndType(final int productId,final int typeId) {
		final List<Askreponse> result = new ArrayList<Askreponse>();
		try {
			jdbcTemplate.query(
				GET_BY_USERID_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setInt(1, productId);
						ps.setInt(2, typeId);
					}
				},
				new RowCallbackHandler() { 
					public void processRow(ResultSet rs) throws SQLException {
						Askreponse to= new Askreponse();
						to.setId(rs.getInt("id"));
						to.setMsgtype(rs.getInt("msgtype"));
						to.setProductId(rs.getInt("productId"));
						to.setAskId(rs.getInt("askId"));
						to.setUser(rs.getString("user"));
						to.setReponseId(rs.getInt("reponseId"));
						to.setReponseName(rs.getString("reponseName"));
						to.setAskcontent(rs.getString("askcontent"));
						to.setReponsecontent(rs.getString("reponsecontent"));
						to.setAsktime(rs.getTimestamp("asktime"));
						to.setReponsetime(rs.getTimestamp("reponsetime"));
						to.setDealFlag(rs.getInt("dealFlag"));
						result.add(to);
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

	String SQL_GET_ACOUNT_PRODUCT=" SELECT COUNT(1) FROM  askreponse a WHERE 1=1 ";
	public int getAllRecorderNumber( final String whereCondition ) {
		final List<Integer> result = new ArrayList<Integer>();
		String sql = SQL_GET_ACOUNT_PRODUCT + whereCondition;
		try {
			jdbcTemplate.query(
					sql,
					new RowCallbackHandler() {
						public void processRow(ResultSet rs) throws SQLException {
							result.add( rs.getInt(1));
						}
					}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		if (result.size()>0 )
			return (result.get(0)).intValue();
		else 
			return 0;
	}
	
	
	String GET_ASK_BY_CONDITION=" SELECT a.* FROM  askreponse a   WHERE  1=1  ";
 
	public List<Askreponse> geAskAccordByCondition(final int beginRecord, final	String whereCondition) {
		final List<Askreponse> result = new ArrayList<Askreponse>();
		String sql = GET_ASK_BY_CONDITION+ whereCondition + "  ORDER BY id LIMIT " + beginRecord + ", "+  (beginRecord+Constant.PAGE_SIZE);
		try {
			 jdbcTemplate.query(
				sql,
				new RowCallbackHandler() {
 					public void processRow(ResultSet rs) throws SQLException {
 						Askreponse to= new Askreponse();
						to.setId(rs.getInt("id"));
						to.setMsgtype(rs.getInt("msgtype"));
						to.setProductId(rs.getInt("productId"));
						to.setAskId(rs.getInt("askId"));
						to.setUser(rs.getString("user"));
						to.setReponseId(rs.getInt("reponseId"));
						to.setReponseName(rs.getString("reponseName"));
						to.setAskcontent(rs.getString("askcontent"));
						to.setReponsecontent(rs.getString("reponsecontent"));
						to.setAsktime(rs.getDate("asktime"));
						to.setReponsetime(rs.getDate("reponsetime"));
						to.setDealFlag(rs.getInt("dealFlag"));
						result.add(to);
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

	
	String GET_ASK_BY_ID_SQL = "  SELECT * FROM  askreponse WHERE  id=? ";
 

	public Askreponse getAskReponseById(final int askreponseRecordId) {
		final List<Askreponse> result = new ArrayList<Askreponse>();
		try {
			jdbcTemplate.query(
					GET_ASK_BY_ID_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setInt(1, askreponseRecordId);
					}
				},
				new RowCallbackHandler() { 
					public void processRow(ResultSet rs) throws SQLException {
						Askreponse to= new Askreponse();
						to.setId(rs.getInt("id"));
						to.setMsgtype(rs.getInt("msgtype"));
						to.setProductId(rs.getInt("productId"));
						to.setAskId(rs.getInt("askId"));
						to.setUser(rs.getString("user"));
						to.setReponseId(rs.getInt("reponseId"));
						to.setReponseName(rs.getString("reponseName"));
						to.setAskcontent(rs.getString("askcontent"));
						to.setReponsecontent(rs.getString("reponsecontent"));
						to.setAsktime(rs.getDate("asktime"));
						to.setReponsetime(rs.getDate("reponsetime"));
						to.setDealFlag(rs.getInt("dealFlag"));
						result.add(to);
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		if ( result.size()>0)
			 return result.get(0);
		else 
			return null;
	}

}
